﻿Imports DTO
Imports System.Data.OleDb
Public Class KhachHangDAO
    Dim conn As OleDbConnection = DataProvider.ConnectDB()
    Public Function ThemKhachHang(ByVal khDTO As KhachHangDTO) As Integer
        Dim sql As String = "Insert Into KHACHHANG (TenKhachHang, CMND, DiaChi) Values(@TenKH,@CMND, @DiaChi)"
        Dim cmd As New OleDbCommand(sql, conn)
        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@TenKH", OleDbType.VarWChar)
        para.Value = khDTO.TenKH

        para = cmd.Parameters.Add("@CMND", OleDbType.WChar)
        para.Value = khDTO.CMND

        para = cmd.Parameters.Add("@DiaChi", OleDbType.VarWChar)
        para.Value = khDTO.DiaChi

        Dim i As Integer = cmd.ExecuteNonQuery()

        sql = "Select @@IDENTITY"
        cmd = New OleDbCommand(sql, conn)
        Dim kq As Integer = cmd.ExecuteScalar()
        conn.Close()
        Return kq
    End Function

    Public Function LayKhacHang() As List(Of KhachHangDTO)
        Dim sql As String = "Select * From KhachHang"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim ds As New List(Of KhachHangDTO)()
        While (reader.Read())
            Dim khDTO As New KhachHangDTO()
            khDTO.MaKH = reader.GetInt32(0)
            khDTO.TenKH = reader.GetString(1)
            khDTO.CMND = reader.GetString(2)
            khDTO.DiaChi = reader.GetString(3)
            ds.Add(khDTO)
        End While
        con.Close()
        Return ds
    End Function
    'Lay khach hang theo ma so
    Public Function LayKhachHangTheoMaSo(ByVal maso As Integer) As KhachHangDTO
        Dim sql As String = "Select MaKhachHang From SoTietKiem Where MaSo=" + maso.ToString() + " And MaTinhTrangSoTietKiem=1"
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim makh As Integer = cmd.ExecuteScalar()

        Dim sql1 As String = "Select * From KHACHHANG Where MaKhachHang=" + makh.ToString()
        Dim cmd1 As New OleDbCommand(sql1, con)
        Dim reader As OleDbDataReader = cmd1.ExecuteReader()
        Dim khDTO As New KhachHangDTO()
        While (reader.Read())
            khDTO.MaKH = reader.GetInt32(0)
            khDTO.TenKH = reader.GetString(1)
            khDTO.CMND = reader.GetString(2)
            khDTO.DiaChi = reader.GetString(3)
        End While
        con.Close()
        Return khDTO
    End Function

    'Tìm kiếm theo mã sổ
    Public Function TimkiemTheoMaSo(ByVal maso As Integer) As SoTietKiemDTO
        Dim sql As String = "Select * From SoTietKiem Where MaSo=" + maso.ToString()
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim stkDTO As New SoTietKiemDTO()
        While (reader.Read())
            stkDTO.MaSo = reader.GetInt32(0)
            stkDTO.MaLoaiTietKiem = reader.GetInt32(1)
            stkDTO.MaKH = reader.GetInt32(2)
            stkDTO.NgayMoSo = reader.GetDateTime(3)
            stkDTO.SoTienGoi = reader.GetDouble(4)
            stkDTO.SoDu = reader.GetDouble(5)
            stkDTO.MaTinhTrangSoTietKiem = reader.GetInt32(6)
        End While
        con.Close()
        Return stkDTO
    End Function

    'Tìm kiếm theo nhiều tiêu trí
    Public Function BuildQuery(ByVal khCrt As KhachHangCrt, ByVal cn As OleDbConnection) As OleDbCommand
        Dim cmd As New OleDbCommand()
        Dim strDKMaKH = " 1=1 "
        Dim strDKTenKH = " 1=1 "
        Dim strDKCMND = " 1=1 "
        Dim strDKDiaChi = " 1=1 "

        If (khCrt.MaKh <> 0) Then
            strDKMaKH = " MaKhachHang = @MaKH "
            cmd.Parameters.Add("@MaKH", OleDbType.Integer)
            cmd.Parameters("@MaKH").Value = khCrt.MaKh
        End If
        If (khCrt.ChkTenKH) Then
            strDKTenKH = " TenKhachHang like ? "
            ' cmd.Parameters.Add("@TenKhachHang", OleDbType.WChar)
            'cmd.Parameters("@TenKhachHang").Value = "%" + khCrt.TenKH + "%"
        End If
        If (khCrt.ChkCMND) Then
            strDKCMND = " CMND like '?' "
            cmd.Parameters.Add("@CMND", OleDbType.WChar)
            cmd.Parameters("@CMND").Value = "%" + khCrt.CMND + "%"

        End If
        If (khCrt.ChkDiaChi) Then
            strDKCMND = " DiaChi like '?' "
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + khCrt.DiaChi + "%"

        End If

        Dim strDKWhere As String = " Where "
        strDKWhere += strDKMaKH
        strDKWhere += " and " + strDKTenKH
        strDKWhere += " and " + strDKCMND
        strDKWhere += " and " + strDKDiaChi

        Dim strSQL As String = "Select * From KhachHang "
        strSQL += strDKWhere
        strSQL += " Order by TenKhachHang "

        cmd.Connection = cn
        cmd.CommandText = strSQL

        Return cmd

    End Function
    'Goi ham tim kiem
    Public Function TimKiem(ByVal khCrt As KhachHangCrt) As List(Of KhachHangCrt)
        Dim dt As New DataTable()
        Dim con As OleDbConnection
        con = DataProvider.ConnectDB()
        Dim cmd As OleDbCommand = BuildQuery(khCrt, con)

        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        Dim dskh As New List(Of KhachHangCrt)()
        While (reader.Read())
            Dim _khCrt As New KhachHangCrt()
            _khCrt.MaKh = reader.GetInt32(0)
            _khCrt.TenKH = reader.GetString(1)
            _khCrt.CMND = reader.GetString(2)
            _khCrt.DiaChi = reader.GetString(3)
            dskh.Add(_khCrt)
        End While
        con.Close()
        Return dskh
    End Function
    'ham cập nhật thông tin khách hàng
    Public Function CapNhatKhachHang(ByVal khDTO As KhachHangDTO) As Integer
        Dim sql As String = "Update KhachHang Set TenKhachHang=?,CMND=?,DiaChi=? Where MaKhachHang=" + khDTO.MaKH.ToString()
        Dim con As OleDbConnection = DataProvider.ConnectDB()
        Dim cmd As New OleDbCommand(sql, con)

        Dim para As OleDbParameter
        para = cmd.Parameters.Add("@TenKH", OleDbType.WChar)
        para.Value = khDTO.TenKH

        para = cmd.Parameters.Add("@CMND", OleDbType.WChar)
        para.Value = khDTO.CMND

        para = cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
        para.Value = khDTO.DiaChi

        Dim kq As Integer = cmd.ExecuteNonQuery()
        con.Close()
        If kq > 0 Then
            Return 1
        Else
            Return 0
        End If

    End Function
End Class
